package com.ld.hpfs.web.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.ld.hpfs.common.jackson.JackJson;
import com.ld.hpfs.common.utils.Const;
import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.pojo.ExtPager;
import com.ld.hpfs.pojo.User;
import com.ld.hpfs.service.DefineReportService;
import com.ld.hpfs.web.interseptor.WebConstants;

@Controller
public class DefineReportController {
	private static final Logger logger = LoggerFactory
			.getLogger(DefineReportController.class);

	@Autowired
	private DefineReportService definereportservice;

	/**
	 * 自定义报告一览
	 * 
	 */
	@RequestMapping(value = "/getReportList")
	public void getTableNameList(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {

		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		//一览页面中全部报告与自己的报告条件
		String owner = (String)request.getParameter("owner") == null ? null : (String)request.getParameter("owner");
		//一览页面中单个报告查看条件
		String report_name = (String)request.getParameter("report_name") == null ? null : (String)request.getParameter("report_name");
		try {
			User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
			if (user != null) {
				//查询所有报告或当前用户报告
				if (owner != null && owner != "") {
					/*如果接受的owner参数为null或空时给owner赋值为null，
					 * 如果为其他参数owner就为当前用户的id*/
					if (Integer.parseInt(owner) == 0) {
						owner = null;
					} else {
						owner = user.getUserId().toString();
					}
				} else {
					owner = null;
				}
				String current_user = user.getUserId();

				param.put("owner", owner);
				param.put("report_name", report_name);
				param.put("pageEnd", page.getStart() + page.getLimit());
				param.put("pageStart", page.getStart());
				ArrayList<HashMap<String, Object>> logs = definereportservice.getReportList(param);
				/*报告查看权限控制,此处创建返回ArrayList*/
				ArrayList<HashMap<String, Object>> returnlogs = new ArrayList<HashMap<String, Object>>();
				String[] allow_user = null;
				/****此处为权限控制，将当前用户可有权限查看的和当前用户创建的报告循环放入返回结果集中*/
				if(logs != null){
					for (int i = 0; i < logs.size(); i++) {
						//取出每条user_id记录并用","拆分为数组与当前登录用户比对
						allow_user = logs.get(i).get("user_id").toString().split(",");
						for (int j = 0; j < allow_user.length; j++) {
							//此处进行比对，如果当前用户为可查看次报告的用户将此条记录添加到返回ArrayList中
							if(allow_user[j].equals(current_user)){
								returnlogs.add(logs.get(i));
								break;
							}
							//判断如果创建人为当前用户将此记录添加到返回ArrayList中
							else if(logs.get(i).get("create_user_id").toString().equals(current_user)){
								returnlogs.add(logs.get(i));
								break;
							}
						}
					}
				}
				
				if (returnlogs != null && returnlogs.size() > 0) {
					result.put("success", true);
					//此处返回ArrayList的size做为记录总条数
					result.put("total", returnlogs.size());
					result.put("data", returnlogs);
				} else {
					result.put("success", true);
					result.put("total", returnlogs.size());
					result.put("data", "");
				}
				String returnMsg = JackJson.fromObjectToJson(result);
				logger.debug("{}", returnMsg);
				writer.print(returnMsg);
			} else {
				writer.print("");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	
	/*
	 * 获取报告名称
	 */
	@RequestMapping(value = "/getreportname")
	public void getReportName(HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		if(user == null){
			return;
		}
		String current_user = user.getUserId();
		ArrayList<HashMap<String, Object>> result = null;
		ArrayList<HashMap<String, Object>> returnresult = new ArrayList<HashMap<String, Object>>();		
		try {
			result = definereportservice.getReportName();
			String[] allow_user = null;
			for (int i = 0; i < result.size(); i++) {
				//取出每条user_id记录并用","拆分为数组与当前登录用户比对
				allow_user = result.get(i).get("user_id").toString().split(",");
				for (int j = 0; j < allow_user.length; j++) {
					//此处进行比对，如果当前用户为可查看次报告的用户将此条记录添加到返回ArrayList中
					if(allow_user[j].equals(current_user)){
						returnresult.add(result.get(i));
					}
				}
			}
			String returnMsg = JackJson.fromObjectToJson(returnresult);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	/*
	 * 获取一览页面报告名称下拉框值
	 */
	@RequestMapping(value = "/getReportNameForCom")
	public void getReportNameForCom(HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		if(user == null){
			return;
		}
		ArrayList<HashMap<String, Object>> result = null;
		try {
			result = definereportservice.getReportNameForCom();
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	/**
	 * 获取表名管理表中已添加的表名
	 * 
	 * @param page
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/getreporttable")
	public void getreporttable(HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		String lang = session.getAttribute(WebConstants.CURRENT_LANG).toString();
		ArrayList<HashMap<String, Object>> result = null;
		String cpn_id = session.getAttribute(WebConstants.CURRENT_COMPANY).toString();
		try {
			if (lang.equals("zh_CN")) {
				result = definereportservice.getReportTableCN(cpn_id);
			} else {
				result = definereportservice.getReportTableEN(cpn_id);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 获取表中所有字段
	 * 
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/gettablefield")
	public void gettablefield(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		String t_name = request.getParameter("t_name");

		HashMap<String, Object> param = new HashMap<String, Object>();
		try {
			param.put("t_name", t_name);
			ArrayList<HashMap<String, Object>> result = definereportservice
					.getTableField(param);
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 获取用户名
	 * 
	 * @param page
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/getuser")
	public void getuser(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		ArrayList<HashMap<String, Object>> result = null;
		try {
			result = definereportservice.getUser();
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 获取用户组
	 * 
	 * @param page
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/getusergroup")
	public void getusergroup(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		ArrayList<HashMap<String, Object>> result = null;
		HashMap<String,Object> param = new HashMap<String,Object>();
		try {
        	String language = (String) session.getAttribute(WebConstants.CURRENT_LANG);
			param.put("language", language);
			result = definereportservice.getUserGroup(param);
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 删除报告
	 * 
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/ReportDelete", method = RequestMethod.POST)
	public void ReportDelete(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		// 报告id
		String report_hid = (String) request.getParameter("report_hid");
		HashMap<String, Object> result = new HashMap<String, Object>();
		HashMap<String, Object> param = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		String[] report_hidlist = report_hid.split(",");
		int action = 0;
		try {
			List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
			for (int i = 0; i < report_hidlist.length; i++) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("report_hid", report_hidlist[i]);
				list.add(map);
			}
			param.put("report_hid", list);
			ArrayList<HashMap<String, Object>> logs = definereportservice
					.CheckReportUser(param);
			int ret = 0;
			// 获取报告创建者
			String create_user = null;
			String user_id = user.getUserId();
			boolean flag = false;
			for (int j = 0; j < logs.size(); j++) {
				create_user = logs.get(j).get("create_user").toString();
				if(!user_id.equals(create_user)){
					flag = false;
					break;
				}else{
					flag = true;
				}
			}

			if (flag) {
				boolean addhistory = false;
				for (int i = 0; i < report_hidlist.length; i++) {
					addhistory = this.AddDefineReportHistory(report_hidlist[i],user,action);
				}
				if(addhistory){
					ret = definereportservice.ReportDelete(param);
					if (ret == 0) {
						result.put("resultcode", 0);
					} else {
						result.put("resultcode", 9);
					}
				}
			} else {
				result.put("resultcode", 1);
			}
			writer.print(JackJson.fromObjectToJson(result));
		} catch (Exception e) {
			logger.error("Exception:", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 编辑按钮 检查当前报告是否为当前用户创建 并给页面个控件赋值
	 * 
	 */
	@RequestMapping(value = "/ReportEdit", method = RequestMethod.POST)
	public void ReportEdit(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		// 报告id
		String report_hid = (String) request.getParameter("report_hid");
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");

		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		try {
			param.put("report_hid", report_hid);

			ArrayList<HashMap<String, Object>> logs = definereportservice
					.ReportEdit(param);

			// 获取报告创建者
			String create_user = logs.get(0).get("create_user").toString();
			// 报告名称
			String report_name = logs.get(0).get("report_name").toString();
			// 表名称
			String table_name = logs.get(0).get("table_name").toString();
			// 报告字段
			String table_field = logs.get(0).get("table_field").toString();
			// 过滤条件
			String filter_condition = logs.get(0).get("filter_condition").toString();
			// 报告描述
			String report_describe = logs.get(0).get("report_describe").toString();
			// 授权用户
			String user_id = logs.get(0).get("user_id").toString();
			// 表名
			String name = logs.get(0).get("name").toString();

			result.put("report_name", report_name);
			result.put("table_name", table_name);
			result.put("table_field", table_field);
			result.put("report_describe", report_describe);
			result.put("user_id", user_id);
			result.put("filter_condition", filter_condition);
			result.put("name", name);

			String current_user = user.getUserId().toString();
			if (current_user.equals(create_user)) {
				result.put("resultcode", 0);
			} else {
				result.put("resultcode", 9);
			}
			writer.print(JackJson.fromObjectToJson(result));
		} catch (Exception e) {
			logger.error("Exception:", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 获取用户组下所属用户
	 * 
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/getUserIdOfGroup")
	public void getUserIdOfGroup(HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		String user_group = request.getParameter("user_group");
		String[] user = user_group.split(",");
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		for (int i = 0; i < user.length; i++) {
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("user_group", user[i]);
			list.add(map);
		}
		HashMap<String, Object> param = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		String user_id = null;
		try {
			param.put("user_group", list);
			ArrayList<HashMap<String, Object>> result = definereportservice
					.getUserIdOfGroup(param);
			for (int i = 0; i < result.size(); i++) {
				user_id = result.get(i).get("user_id").toString();
				if (sb.length() > 0) {
					sb.append(",");
				}
				sb.append(user_id);
			}
			String returnMsg = JackJson.fromObjectToJson(sb);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 保存报告
	 * 过滤条件的保存格式为 例：两个过滤条件 a,<,'1';b,>,'2'
	 * 此种格式方便报告查看，可将字段和过滤条件从数据库查询出之后替换掉"；"和"，"直接拼接到sql中执行
	 * @param session
	 * @param request
	 * @param response
	 * @param writer
	 */
	@RequestMapping(value = "/ReportEditSave")
	public void ReportEditSave(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		// 表名称
		String table_name = request.getParameter("table_name");
		// 报告名称
		String report_name = request.getParameter("report_name");
		// 报告描述
		String report_describe = request.getParameter("report_describe");
		// 过滤条件
		String filter_condition = request.getParameter("filter_condition");
		if(!(filter_condition == "")){
			filter_condition = filter_condition.substring(1,filter_condition.length() - 1);
		}
		// 显示字段
		String table_field = request.getParameter("table_field");
		table_field = table_field.substring(1, table_field.length() - 1);
		// 授权用户
		String user_id = request.getParameter("user_id");
		user_id = user_id.substring(1, user_id.length() - 1);
		// 获取当前用户
		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		String create_user = user.getUserId().toString();
		// 获取时间
		Date date = new Date();
		java.text.DateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String create_time = format.format(date);
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		try {
			param.put("table_name", table_name);
			param.put("report_name", report_name);
			param.put("report_describe", report_describe);
			param.put("filter_condition", filter_condition);
			param.put("table_field", table_field);
			param.put("user_id", user_id);
			param.put("create_user", create_user);
			param.put("create_time", create_time);
			int ret = 0;
			ret = definereportservice.ReportSave(param);
			if (ret == 0) {
				result.put("resultcode", 0);
			} else {
				result.put("resultcode", 9);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	//报告修改
	@RequestMapping(value = "/ReportEditEdit")
	public void ReportEditEdit(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		// 报告id
		String report_hid = request.getParameter("report_hid");
		// 表名称
		String table_name = request.getParameter("table_name");
		// 报告名称
		String report_name = request.getParameter("report_name");
		// 报告描述
		String report_describe = request.getParameter("report_describe");
		// 过滤条件
		String filter_condition = request.getParameter("filter_condition");
		if(!(filter_condition == "")){
			filter_condition = filter_condition.substring(1,filter_condition.length() - 1);
		}
		// 显示字段
		String table_field = request.getParameter("table_field");
		table_field = table_field.substring(1, table_field.length() - 1);
		// 授权用户
		String user_id = request.getParameter("user_id");
		user_id = user_id.substring(1, user_id.length() - 1);
		// 获取当前用户
		User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
		String create_user = user.getUserId().toString();
		// 获取时间
		Date date = new Date();
		java.text.DateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String create_time = format.format(date);
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		int action = 1;
		try {
			param.put("report_hid", report_hid);
			param.put("table_name", table_name);
			param.put("report_name", report_name);
			param.put("report_describe", report_describe);
			param.put("filter_condition", filter_condition);
			param.put("table_field", table_field);
			param.put("user_id", user_id);
			param.put("create_user", create_user);
			param.put("create_time", create_time);
			boolean flag = this.AddDefineReportHistory(report_hid, user, action);
			if(flag){
				int ret = 0;
				ret = definereportservice.ReportEditEdit(param);
				if (ret == 0) {
					result.put("resultcode", 0);
				} else {
					result.put("resultcode", 9);
				}
				String returnMsg = JackJson.fromObjectToJson(result);
				logger.debug("{}", returnMsg);
				writer.print(returnMsg);
			}else{
				writer.print("");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

	/**
	 * 检查过滤条件是否正确，防止查看时因条件报错导致页面不正常
	 */
	@RequestMapping(value = "/getFieldProperty")
	public void getnsdihfuishd(HttpSession session,
			HttpServletRequest request, 
			HttpServletResponse response,PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		
		String table_name = request.getParameter("tablename");
		String table_field = request.getParameter("field");
		String inputvalue = request.getParameter("inputvalue");
				
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		try {
			param.put("table_name", table_name);
			param.put("table_field", table_field);
			
			boolean islegal = false;
			
			try {
				HashMap<String, Object> properties = definereportservice.getFieldProperty(param);
				String property = properties.get("property").toString();
				
				if (property.equals("datetime")) {
					islegal = isDate(inputvalue,"yyyyMMdd") || isDate(inputvalue,"yyyy-MM-dd") || isDate(inputvalue,"yyyy/MM/dd");
				} else if (property.equals("int") || property.equals("decimal")) {
					try {
						Integer.parseInt(inputvalue);
						islegal = true;
					} catch (Exception e) {
						islegal = false;
					}
				} else if (property.equals("bit")) {
					if (inputvalue.equals("0") || inputvalue.equals("1")) {
						islegal = true;
					}
				} else if (property.equals("string")) {
					islegal = true;
				}
				result.put("resultcode", 0);
			} catch (Exception e) {
				logger.debug(e.getMessage());
				result.put("resultcode", 1);
			}
			
			result.put("islegal", islegal);
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	public static boolean isDate(String str_input,String rDateFormat){
         SimpleDateFormat formatter = new SimpleDateFormat(rDateFormat);
         formatter.setLenient(false);
         try {
             formatter.format(formatter.parse(str_input));
         } catch (Exception e) {
             return false;
         }
         return true;
	}
	
	/**
	 * 检查过滤条件是否正确，防止查看时因条件报错导致页面不正常
	 */
	@RequestMapping(value = "/checkCondition")
	public void checkCondition(ExtPager page,HttpSession session,
			HttpServletRequest request, 
			HttpServletResponse response,PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		
		// 前台接受的报告id
		String report_hid = request.getParameter("report_hid");
		//前台参数存放HashMap
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		HashMap<String, Object> paramforcount = new HashMap<String, Object>();
		try {
			param.put("report_hid", report_hid);
			//从自定义报告表中获取前台中编辑的表明、表字段、过滤条件  TAX_D_REPORT表
			ArrayList<HashMap<String, Object>> logs = definereportservice.getReportColume(param);
			// 表名称
			String table_name = logs.get(0).get("table_name").toString();
			//从返回数据中取得过滤条件
			String filter_condition = logs.get(0).get("filter_condition").toString();
			
			if(filter_condition != null && filter_condition != ""){
				if(filter_condition.contains(";")){
					filter_condition = filter_condition.replaceAll(";", " and ");
					filter_condition = filter_condition.replaceAll(",", " ");
				}else{
					filter_condition = filter_condition.replaceAll(",", " ");
				}
			}else{
				filter_condition = null;
			}
			
			paramforcount.put("table_name", table_name);
			paramforcount.put("filter_condition", filter_condition);
			try {
				definereportservice.getTotalCount(paramforcount);
				result.put("resultcode", true);
			} catch (Exception e) {
				logger.error("Exception: ", e.getMessage());
				result.put("resultcode", false);
			}
			String returnMsg = JackJson.fromObjectToJson(result);
			logger.debug("{}", returnMsg);
			writer.print(returnMsg);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	/**
	 * 报告查看
	 * 获取报告查看数据及column表头
	 */
	@RequestMapping(value = "/getreportcheck")
	public void getreportcolume(ExtPager page,HttpSession session,
			HttpServletRequest request, 
			HttpServletResponse response,PrintWriter writer) {
		response.setContentType("text/plain;charset=UTF-8");
		
		
		int page_start = Integer.parseInt(request.getParameter("page_start") == "" ? "1" : request.getParameter("page_start"));
		int page_limit = Integer.parseInt(request.getParameter("page_limit") == "" ? "30" : request.getParameter("page_limit"));
		// 前台接受的报告id
		String report_hid = request.getParameter("report_hid");
		// 拼接后的返回值
		StringBuffer combainresult = new StringBuffer();
		//前台参数存放HashMap
		HashMap<String, Object> param = new HashMap<String, Object>();
		//一览表头
		StringBuffer columns = new StringBuffer();
		//一览索引
		StringBuffer fields = new StringBuffer();
		//一览数据
		String data = "";
		//一览数据行数
		StringBuffer totalRecord = new StringBuffer();
		//自定义报告表中存放的表名、字段名、过滤条件存放到次HashMap，拼接查询数据sql
		HashMap<String, Object> paramfordata = new HashMap<String, Object>();
		try {
			PrintWriter out = response.getWriter();
			param.put("report_hid", report_hid);
			//从自定义报告表中获取前台中编辑的表明、表字段、过滤条件  TAX_D_REPORT表
			ArrayList<HashMap<String, Object>> logs = definereportservice.getReportColume(param);
			//从返回数据中取得表名
			String table_name = logs.get(0).get("table_name").toString();
			//从返回数据中取得字段
			String table_field = logs.get(0).get("table_field").toString();
			//从返回数据中取得过滤条件
			String filter_condition = logs.get(0).get("filter_condition").toString();
			if(filter_condition != null && filter_condition != ""){
				if(filter_condition.contains(";")){
					filter_condition = filter_condition.replaceAll(";", " and ");
					filter_condition = filter_condition.replaceAll(",", " ");
				}else{
					filter_condition = filter_condition.replaceAll(",", " ");
				}
			}else{
				filter_condition = null;
			}
			
			String orderby = "";
			if (orderby.contains(",")) {
				orderby = table_field.substring(0, table_field.indexOf(","));
			} else {
				orderby = table_field.substring(0, table_field.length());
			}
			paramfordata.put("pageEnd", page_start + page_limit - 1);
			paramfordata.put("pageStart", page_start);
			
			paramfordata.put("orderby", orderby);
			
			paramfordata.put("table_name", table_name);
			paramfordata.put("table_field", table_field);
			paramfordata.put("filter_condition", filter_condition);
			
			ArrayList<HashMap<String, Object>> sqlresult = definereportservice.getreportstore(paramfordata);
			
			String[] table_field_list = table_field.split(",");
			columns.append("{");
			columns.append("'fields': [");
			fields.append("'columns': [");
			data += "'data': ";
			
			//将fields和columns拼接成json格式
			for(int i = 0; i<table_field_list.length; i++){
				if(i > 0){
					columns.append(",");
					fields.append(",");
				}
				columns.append("'" + table_field_list[i] + "'");
				fields.append("{header: '" + table_field_list[i] + "',align : 'center',dataIndex: '" + table_field_list[i] + "'}");
			}
			columns.append("],");
			fields.append("],");
			
			// 判断是否是日期
			for (int i = 0; i < sqlresult.size(); i++) {
				Object[] bbb = sqlresult.get(i).keySet().toArray();
				for (int j = 0; j < bbb.length; j++) {
					String key = bbb[j].toString();
					if (key.contains("_TIME") || key.contains("_time")) {
						String value = sqlresult.get(i).get(key).toString();
						if (value.contains("-") && value.contains(" ") && value.contains(":")) {
							value = value.substring(0, 19);
							sqlresult.get(i).remove(key);
							sqlresult.get(i).put(key, value);
						}
					}
				}
			}
			//将数据封装成JSON格式
			data += JackJson.fromObjectToJson(sqlresult);
			data += ",";
			int count = definereportservice.getTotalCount(paramfordata);
			//一览行数
			totalRecord.append("'totalRecord':" + sqlresult.size() + ",'count':" + count + ",'success':true");
			
			combainresult.append(columns);
			combainresult.append(fields);
			combainresult.append(data);
			combainresult.append(totalRecord);
			combainresult.append("}");
			
			logger.debug("{}", combainresult);
			out.print(combainresult);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	//自定义报告数据导出
	@RequestMapping(value = "/defineReportExport")
	public void defineReportExport(HttpSession session, HttpServletRequest request,
			HttpServletResponse response, PrintWriter writer) {
		// 报告id
		String report_hid = request.getParameter("report_hid");
		HashMap<String, Object> param = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		HashMap<String, Object> paramfordata = new HashMap<String, Object>();
		try {
			param.put("report_hid", report_hid);
			//从自定义报告表中获取前台中编辑的表明、表字段、过滤条件  TAX_D_REPORT表
			ArrayList<HashMap<String, Object>> logs = definereportservice.getReportColume(param);
			//从返回数据中取得表名
			String table_name = logs.get(0).get("table_name").toString();
			//从返回数据中取得字段
			String table_field = logs.get(0).get("table_field").toString();
			String[] table_field_list = table_field.split(",");
			
			StringBuffer sb = new StringBuffer();
			String fieldtemp = "";
			// 给字段添加isnull判断 否则当导出时有字段为null值时在sqlresult查询返回集中会自动去掉为null值得一列 使数据不能对应标题而报错
			for (int i = 0; i < table_field_list.length; i++) {
				if (i > 0) {
					sb.append(", ");
				}
				fieldtemp = table_field_list[i];
				sb.append(fieldtemp);
			}
			
			//从返回数据中取得过滤条件
			String filter_condition = logs.get(0).get("filter_condition").toString();
			if(filter_condition != null && filter_condition != ""){
				if(filter_condition.contains(";")){
					filter_condition = filter_condition.replaceAll(";", " and ");
					filter_condition = filter_condition.replaceAll(",", " ");
				}else{
					filter_condition = filter_condition.replaceAll(",", " ");
				}
			}else{
				filter_condition = null;
			}
			paramfordata.put("table_name", table_name);
			paramfordata.put("table_field", sb);
			paramfordata.put("filter_condition", filter_condition);
			
			// 取得此报告的数据
			ArrayList<HashMap<String, Object>> sqlresult = definereportservice.getreportstore(paramfordata);
			// 创建excel
			HSSFWorkbook workbook = new HSSFWorkbook();
	        // 创建工作表和标题
	        HSSFSheet sheet = workbook.createSheet(); // 创建工作区
	        
	        // 创建数据表头
	        String[] titles = table_field.split(",");
	        
	        HSSFRow row = sheet.createRow(0);
	        HSSFCellStyle items_style = workbook.createCellStyle();
	        items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

	        HSSFFont celltbnamefont = workbook.createFont();
	        celltbnamefont.setFontHeightInPoints((short) 10);

	        celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
	        items_style.setFont(celltbnamefont);
	        items_style.setWrapText(true);

	        for (int i = 0; i < titles.length; i++) {
	            HSSFCell cell = row.createCell(i);
	            sheet.setColumnWidth(i, 5335);
	            cell.setCellValue(titles[i]);
	            cell.setCellStyle(items_style);
	        }

	        HSSFCellStyle datestyle = workbook.createCellStyle();
	        HSSFDataFormat df = workbook.createDataFormat();
	        datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));

	        int rowNum = 1;
	        for (HashMap<String,Object> entry : sqlresult) {
	            // create the row data
	            HSSFRow datarow = sheet.createRow(rowNum++);
	            for(int i = 0; i < titles.length; i++){
	            	String tit = titles[i].toString();
	            	datarow.createCell(i).setCellValue(entry.get(tit) == null ? "" : entry.get(tit).toString());
	            }
	        }
	        // 获取项目所在路径 写入文件
        	String strPath = Const.REAL_PATH;
        	String strfilePath = null;
        	if(strPath.contains("tomcat-7.0")){
            	strfilePath = strPath.substring(0, strPath.indexOf("tomcat-7.0")) + "download//";
            	File file = new File(strfilePath+"DefineReport.xls");
        		FileOutputStream os = new FileOutputStream(file);  
        		workbook.write(os);
        		os.flush();
        		os.close();
        	}
        	// 将文件保存到本地
        	OutputStream out = new FileOutputStream(strfilePath+"DefineReport.xls");
        	workbook.write(out);
            out.close();
            
            // 读取文件并放入文件流中下载
            InputStream inStream = new FileInputStream(strfilePath+"DefineReport.xls");
            response.reset();
            response.setContentType("bin");
            response.addHeader("Content-Disposition", "attachment;   filename=\"" + table_name + ".xls" + "\"");
            byte[] b = new byte[100];
            int len;
            while ((len = inStream.read(b)) > 0) {
                response.getOutputStream().write(b, 0, len);
            }
            inStream.close();

            // 删除文件
            File file = new File(strfilePath+"DefineReport.xls");
            file.delete();
            
            writer.print("sueecss");
		} catch (Exception e) {
			logger.error("Exception:", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}
	
	/**
	 * 添加表名管理履历
	 * @param t_name 已添加的表名，根据此参数查询出要删除的数据
	 * @param user 当前用户，用来添加执行操作的用户id
	 * @param action 0为删除，1为修改
	 * @return
	 */
	private boolean AddDefineReportHistory(String report_hid, User user, int action) {
		try{
			HashMap<String,Object> history_param = new HashMap<String,Object>();
			
			history_param.put("report_hid", report_hid);
			history_param.put("action", action);
			history_param.put("user_id", user.getUserId());
			history_param.put("user_name", user.getAccount());
			history_param.put("update_time", Tools.getCurrentDay());
			definereportservice.AddDefineReportHistory(history_param);
			return true;
		}catch(Exception e){
			return false;
		}
	}
}